# Copyright (C) 2006 Numbler LLC
# See LICENSE for details.
#
# Export sheets in various formats
#


from numbler.server import sheet,engine
from nevow import rend,tags as T,loaders,inevow
from nevow.util import log
from twisted.internet.defer import deferredGenerator,waitForDeferred,Deferred
from twisted.internet import reactor
from server.ssdb import ssdb
import time
import types
from itertools import *
from utils import yieldDef
import csv
from StringIO import StringIO
from numbler.server.localedb import ParseCtx
from numbler.server.littools import serialDateToISO

class msExcelExport(rend.Page):
    """
    Export a Numbler sheet in the Microsoft office XML format.
    """

    docFactory = loaders.xmlfile("excelxml_export_template.xml")
    
    prolog = """<?xml version="1.0" encoding="UTF-8"?>\n<!-- generated by Numbler.com -->\n"""

    rowPattern = inevow.IQ(docFactory).patternGenerator("row")
    colPattern = inevow.IQ(docFactory).patternGenerator("column")
    cellPattern = inevow.IQ(docFactory).patternGenerator("cell")
    celldataPattern = inevow.IQ(docFactory).patternGenerator("celldata")
    stylePattern = inevow.IQ(docFactory).patternGenerator("style")
    fontPattern =  inevow.IQ(docFactory).patternGenerator("font")
    interiorPattern = inevow.IQ(docFactory).patternGenerator("interior")
    alignmentPattern = inevow.IQ(docFactory).patternGenerator("alignment")

    numberTypes = [types.IntType,types.FloatType]

    # TODO: should extract this from the template. styleBase indicates
    # the base of the first user specified styles
    styleBase = 10
    styleDict = {ParseCtx.commaFormat:'s1',
                 ParseCtx.currencyFormat:'s2',
                 ParseCtx.percentFormat:'s3',
                 ParseCtx.shortDateFormat:'s4',
                 ParseCtx.dateTimeFormat:'s5',
                 ParseCtx.mediumTimeFormat:'s6',
                 ParseCtx.scientificFormat:'s7',
                 ParseCtx.longTimeFormat:'s8',
                 ParseCtx.veryshortMonthDayFormat:'s4',
                 ParseCtx.veryshortMonthYearFOrmat:'s4'
                 }

    def __init__(self,sheetUID):
        self.sheetUID = sheetUID
        
    @deferredGenerator
    def loadSheetInfo(self,arg):
        """ this uses the deferredGenerator idiom to support yielding
        to the reactor during processing
        """
        
        self.dbsheet = sheet.SheetHandle.getInstance(self.sheetUID)
        self.sheetname = self.dbsheet().getAlias()
        self.extents = self.dbsheet().getDimensions()
        sheetObj = self.dbsheet()

        self.rows = {}
        self.styles = []
        self.styleMapper = {}

        yieldCount = 0

        for rowprop in sheetObj.getRowProps():
            fmt = rowprop.getFormat()
            if fmt:
                # this is slightly broken... why on rows and cols is the style under cache?
                self.styles.append(fmt['cache'])
                self.styleMapper[rowprop.getKey()] = len(self.styles)-1
                self.rows[rowprop.getId()] = []
            yieldCount += 1
            if yieldCount % 10 == 0:
                yield waitForDeferred(yieldDef())


        for colprop in sheetObj.getColumnProps():
            fmt = colprop.getFormat()
            if fmt:
                # this is slightly broken... why on rows and cols is the style under cache?
                self.styles.append(fmt['cache'])
                self.styleMapper[colprop.getKey()] = len(self.styles)-1
            yieldCount += 1
            if yieldCount % 10 == 0:
                yield waitForDeferred(yieldDef())                
        
        for cellH in sheetObj.safeGetCellHandleIter():
            yieldCount += 1            
            cList = self.rows.get(cellH.getRow())
            if cList is None:
                cList = []
                self.rows[cellH.getRow()] = cList
            cList.append(cellH)
            fmt = cellH().getFormat()
            if fmt:
                style = {}
                style.update(fmt)
                # check for row and column styling.  Excel doesn't do inheritance
                # if the cell has additional formatting.
                rowS = self.styleMapper.get('r' + str(int(cellH.getRow())))
                if rowS is not None:
                    style.update(self.styles[rowS])
                colS = self.styleMapper.get('c' + str(int(cellH.getCol())))
                if colS is not None:
                    style.update(self.styles[colS])
                self.styles.append(style)
                self.styleMapper[cellH.getKey()] = len(self.styles)-1

            # yield to the reactor
            if yieldCount % 10 == 0:
                yield waitForDeferred(yieldDef())                            

        # FIXME: using private data
        self.rowpropdict = sheetObj._rowProps
        #log.msg('%s %s' % (time.asctime(),'done processing db rep'))

    def bindStyleId(self,attrs,key):
        """ lookup a style attribute based on the given key """
        mapper = self.styleMapper.get(key)
        if mapper is not None:
            attrs['ss:StyleID'] = 's' + str(mapper+self.styleBase)
            return True
        return False

    def renderHTTP(self,ctx):
        request = inevow.IRequest(ctx)
        ctype = ctx.arg('plainxml')
        if ctype and ctype in ('true','1'):
            request.setHeader('content-type','text/html; charset=UTF-8')
        else:
            request.setHeader('content-type','application/vnd.ms-excel; charset=UTF-8')

        request.write(self.prolog)
        return rend.Page.renderHTTP(self,ctx)        

    def beforeRender(self,ctx):
        d = Deferred()
        d.addCallback(self.loadSheetInfo)
        reactor.callLater(0,d.callback,None)
        return d

    def render_author(self,ctx,data):
        return 'Joe'

    def render_created(self,ctx,data):
        return time.strftime("%Y-%m-%dT%H:%M:%SZ",time.gmtime())

    def render_pi(self,ctx,data):
        return '<?xml version="1.0" encoding="UTF-8"?>'

    def render_worksheet(self,ctx,data):
        ctx.fillSlots('sheetname',self.sheetname)
        return ctx.tag

    @deferredGenerator
    def render_table(self,ctx,data):
        """ renders the main part of the sheet.  The excel XML
        format renders rows with columns contained each row.
        """
        cols,rows = ([],[])
        #log.msg('%s %s' % (time.asctime(),'start rending table'))        
        ctx.fillSlots('colcount',int(self.extents[0]))
        ctx.fillSlots('rowcount',self.extents[1])

        for i in sorted(self.dbsheet().getColumnProps()):
            cols.append(self.colPattern(data=i))
            yield waitForDeferred(yieldDef())

        for i in sorted(self.rows.keys()):
            rows.append(self.rowPattern(data=i))
            yield waitForDeferred(yieldDef())

        #log.msg('%s %s' % (time.asctime(),'done rendering table'))
        yield ctx.tag[cols,rows]
#            [self.colPattern(data=i) for i in sorted(self.dbsheet().getColumnProps())],
#            [self.rowPattern(data=i) for i in sorted(self.rows.keys())]]


    @deferredGenerator
    def render_row(self,ctx,data):
        """
        render an individual row.
        Optional attributes:

        ss:Height = custom row height
        style information
        """
        
        ctx.fillSlots('index',data)
        attribs = {}
        rowprop = self.rowpropdict.get(data)
        if rowprop is not None:
            attribs['ss:Height'] = rowprop.getHeight()
            self.bindStyleId(attribs,rowprop.getKey())


        celldata = sorted(self.rows[data],lambda x,y: cmp(x.getCol(),y.getCol()))

        # expand attribs so that it appears that we are calling in the
        # for foo=bar,blah=bing, etc
        cellret = []

        for i in celldata:
            cellret.append(self.cellPattern(data=i))
            yield waitForDeferred(yieldDef())

        yield ctx.tag(**attribs)[cellret]

    def render_column(self,ctx,data):
        """
        render an individual column.  Columns are only
        necessary if they have a style OR a custom width

        optional attributes:
        Style
        """

        ctx.fillSlots('index',data.getId())
        # todo: fix the fudge factor
        ctx.fillSlots('width',data.getWidth())
        attribs = {}
        self.bindStyleId(attribs,data.getKey())
        return ctx.tag(**attribs)

    def render_cell(self,ctx,data):
        """ render an excel cell element

        optional attributes:

        ss:StyleID="sxx".  This is the key into the style table.
        ss:Formula="formula".  The formula must be in R1C1 format.
        others??
        """
        cellInst = data()
        attrs = {}
        formula = cellInst.getFormula()
        if formula and formula[0] == '=':
            # TODO: do R1C1 formula translation
            attrs['ss:Formula'] = cellInst.getFormulaR1C1()

        self.bindStyleId(attrs,data.getKey())
        
        ctx.fillSlots('colindex',int(data.getCol()))
        value = cellInst.getValue()
        
        return ctx.tag(**attrs)[self.celldataPattern(data= (value,formula,cellInst.numFormat()))]


    def render_celldata(self,ctx,data):
        """
        render the data in a cell.
        This should be empty if the cell is only styled.
        If the cell has a formula this should be the calculated result
        """
        value,formula,fmt = data
        if value != formula:
            final = value
            if fmt is not None and fmt in ParseCtx.dateIds:
                # convert to the ISO time format
                final = unicode(serialDateToISO(value))
                dtype = 'DateTime'
            elif isinstance(value,(int,float,long)):
                dtype = 'Number'
            elif isinstance(value,ValueError):
                dtype = "Error"
                final = unicode(str(value))
            else:
                dtype = 'String'
        else:
            final = formula
            dtype = 'String'
            
        ctx.fillSlots('datatype',dtype)
        return ctx.tag[final]
    
    @deferredGenerator
    def render_styles(self,ctx,data):
        """ render all of the possible style elements """
        ret = []
        for x in zip(range(0,len(self.styles)),self.styles):
            ret.append(self.stylePattern(data=x))
            yield waitForDeferred(yieldDef())            

        #log.msg('%s %s' % (time.asctime(),'done rendering styles'))        
        yield ctx.tag[ret]

    @deferredGenerator
    def render_style(self,ctx,data):
        """ render style information that is later bound to a cell,row,or column

        sample:

        <Style ss:ID='s21'>
        <Font ss:Color='#3366FF'/>
        <Interior ss:Color='#FFFF00' ss:Pattern='Solid'/>
        </Style>
        """

        ctx.fillSlots('id','s' + str(data[0]+self.styleBase))
        fmt = data[1]
        #print fmt
        filldata = []

        # process fonts
        res = self.processFont(fmt)
        if len(res):
            filldata.append(self.fontPattern(data=res))

        bgColor = fmt.get('background-color')
        if bgColor:
            filldata.append(self.interiorPattern(data=bgColor))
        textAlign = fmt.get('text-align')
        if textAlign:
            filldata.append(self.alignmentPattern(data=textAlign))

        attribs = {}
        numfmt = fmt.get('__sht')
        if numfmt:
            parent = self.styleDict.get(numfmt)
            if parent is not None:
                attribs['ss:Parent'] = parent

        #log.msg('%s %s' % (time.asctime(),'done rendering style'))
        yield waitForDeferred(yieldDef())
        yield ctx.tag(**attribs)[filldata]

    def processFont(self,fmt):
        """ generate attributes for the Font element """
        attribs = {}

        color = fmt.get('color')
        if color:
            attribs['ss:Color'] = color
        fweight = fmt.get('font-weight')
        if fweight and fweight == 'bold':
            attribs['ss:Bold'] = 1
        underline = fmt.get('text-decoration')
        if underline and underline == 'underline':
            attribs['ss:Underline'] = 'Single'
        italic = fmt.get('font-style')
        if italic and italic == 'italic':
            attribs['ss:Italic'] = 1

        return attribs

    def render_font(self,ctx,data):
        return ctx.tag(**data)

    def render_interior(self,ctx,data):
        ctx.fillSlots('color',data)
        return ctx.tag

    alignDict = {'center':'Center','left':'Left','right':'Right'}
    
    def render_alignment(self,ctx,data):
        
        fill = self.alignDict.get(data.lower())
        if fill is None:
            fill = ''
        ctx.fillSlots('horizontal',fill)
        return ctx.tag





class CSVExport(rend.Page):
    """
    Export a numbler spreadsheet to CSV format.  CSV only supports
    cell values and does not support formatting or formulas.
    
    CSV is a very simple (and fairly useless format).
    
    Empty rows are represented by a linefeed (\r\n)
    Empty columns are represented by a ,
    Column values are seperated by a comma (duh)
    
    """

    def __init__(self,sheetUID):
        self.sheetUID = sheetUID

    def renderHTTP(self,ctx):
        request = inevow.IRequest(ctx)
        ctype = ctx.arg('html')
        if ctype and ctype in ('true','1'):
            pass
        else:
            request.setHeader('content-type','text/csv; charset=UTF-8')

        return self.generate()

    @deferredGenerator
    def generate(self):
        """ generate the CSV text.  this uses the deferredGenenerator idiom
        to avoid blocking the server.  whenever the waitForDeffered clas
        is yield the reactor will have a chance to run
        """

        dbsheet = sheet.SheetHandle.getInstance(self.sheetUID)
        sheetname = dbsheet().getAlias()
        extents = dbsheet().getDimensions()
        sheetObj = dbsheet()

        buffer = []
        rows = {}        
        # step 1: organize the cells by row.
        yieldcount = 0
        
        for cellH in sheetObj.safeGetCellHandleIter():
            cList = rows.get(cellH.getRow())
            if cList is None:
                cList = {}
                rows[cellH.getRow()] = cList
            value = cellH().getValue()
            if value:
                cList[cellH.getCol()] = str(value)
            yieldcount += 1
            if yieldcount % 10 == 0:
                yield waitForDeferred(yieldDef())

        maxrow = extents[1]

        # step 2: write the data using the python CSV writer class.  this handles
        # embedded commas and quoted strings (whew)
        csvdata = StringIO('')
        writer = csv.writer(csvdata)
        for row in range(1,maxrow+1):
            # if we have no data for the cell write an empty list
            rowdata = rows.get(row)
            if not rowdata or (rowdata and len(rowdata) == 0):
                writer.writerow([])
                continue
            # the list comprehension ensures that the entire row is written out ('' for empty cells)
            writer.writerow([rowdata.has_key(col) and rowdata[col] or '' for col in range(1,max(rowdata)+1)])
            yield waitForDeferred(yieldDef())   

        yield csvdata.getvalue()
